﻿using System;
using System.Collections.ObjectModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace Portal.Ws.Uni.Core
{
    public class UniSqlDatabase
    {
        public UniSqlDatabase()
        {

        }

        string _ConnectionString = "";
        public string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
            set
            {
                _ConnectionString = value;
            }
        }


        public UniSqlDatabase(string ConnectionString)
        {
            _ConnectionString = ConnectionString;
        }

        public DataTable GetRecordSet(string Query)
        {
            DataTable dtResult = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dtResult);
            return dtResult;
        }

        public DataTable GetRecordSet(string Query, CommandType sqlCommandType)
        {
            DataTable dtResult = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = sqlCommandType;
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dtResult);
            return dtResult;
        }



        public DataTable GetRecordSet(string Query, DatabaseParamCls[] DatabaseParams)
        {
            DataTable dtResult = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dtResult);
            return dtResult;
        }




        public DataTable GetRecordSetAutoAppendParam(
            string Query,
            Collection<DatabaseParamCls> DatabaseParams,
            string AppendOrderQuery)
        {
            DataTable dtResult = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            for (int iIndex = 0; iIndex < DatabaseParams.Count; iIndex++)
            {
                if (DatabaseParams[iIndex].Value != null)
                {
                    if (!string.IsNullOrEmpty(DatabaseParams[iIndex].Value.ToString()))
                    {
                        if (DatabaseParams[iIndex].UseLikeQuery)
                        {
                            Query += " and " + DatabaseParams[iIndex].GetField + " LIKE N'%" + DatabaseParams[iIndex].Value + "%'";
                        }
                        else
                        {
                            Query += " and " + DatabaseParams[iIndex].GetField + "=@" + DatabaseParams[iIndex].FieldName;
                        }
                    }
                }
            }
            Query += " " + AppendOrderQuery;
            sqlCommand.CommandText = Query;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Count; iIndex++)
                {
                    if (DatabaseParams[iIndex].UseLikeQuery == false)
                    {
                        if (DatabaseParams[iIndex].Value != null)
                        {
                            if (!string.IsNullOrEmpty(DatabaseParams[iIndex].Value.ToString()))
                            {
                                string SqlField = DatabaseParams[iIndex].FieldName;
                                sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                                sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                            }
                        }
                    }
                }
            }
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dtResult);
            return dtResult;
        }


        public DataTable GetRecordSetAutoAppendParam(
            string Query,
            Collection<DatabaseParamCls> DatabaseParams)
        {
            return GetRecordSetAutoAppendParam(Query, DatabaseParams, "");
        }


        public DataTable GetRecordSet(string Query, DatabaseParamCls[] DatabaseParams, CommandType sqlCommandType)
        {
            DataTable dtResult = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = sqlCommandType;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dtResult);
            return dtResult;
        }

        public DataSet GetRecordSetByDataSet(string Query)
        {
            DataSet dsResult = new DataSet();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dsResult);
            return dsResult;
        }


        public DataSet GetRecordSetByDataSet(string Query, CommandType sqlCommandType)
        {
            DataSet dsResult = new DataSet();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = sqlCommandType;
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dsResult);
            return dsResult;
        }


        public DataSet GetRecordSetByDataSet(string Query, DatabaseParamCls[] DatabaseParams)
        {
            DataSet dsResult = new DataSet();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dsResult);
            return dsResult;
        }


        public DataSet GetRecordSetByDataSet(string Query, DatabaseParamCls[] DatabaseParams, CommandType sqlCommandType)
        {
            DataSet dsResult = new DataSet();
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = sqlCommandType;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommand);
            sqlda.Fill(dsResult);
            return dsResult;
        }


        public void ExecuteQuery(object oTransaction, string ConnectionString, DatabaseParamCls[] DatabaseParams, string Query)
        {
            try
            {
                InternalExecuteQuery(oTransaction, ConnectionString, DatabaseParams, Query, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void ExecuteQuery(object oTransaction, string ConnectionString, DatabaseParamCls[] DatabaseParams, string Query, CommandType cmdType)
        {
            try
            {
                InternalExecuteQuery(oTransaction, ConnectionString, DatabaseParams, Query, cmdType);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        void InternalExecuteQuery(object oTransaction, string ConnectionString, DatabaseParamCls[] DatabaseParams, string Query, CommandType ExeCommandType)
        {
            if (ConnectionString != null)
            {
                _ConnectionString = ConnectionString;
            }
            bool HasTrans = false;
            SqlConnection sqlConnection = null;
            if (oTransaction == null)
            {
                HasTrans = false;
                sqlConnection = new SqlConnection(_ConnectionString);
                sqlConnection.Open();
                oTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = ((SqlTransaction)oTransaction).Connection;
                HasTrans = true;
            }

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = (SqlTransaction)oTransaction;
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = ExeCommandType;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false) ((SqlTransaction)oTransaction).Commit();
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    ((SqlTransaction)oTransaction).Rollback();
                }
                string Msg = ex.Message.ToString();
                Msg = CorrectMsg(Msg);
                throw new Exception(Msg);
            }
        }

        string CorrectMsg(string Msg)
        {
            if (Msg.IndexOf("unique index") != -1)
            {
                return "Trùng dữ liệu đã có! Xin vui lòng kiểm tra lại!";
            }
            else
            {
                return Msg;
            }
        }

        public object BeginTransaction()
        {
            SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();
            return sqlConnection.BeginTransaction();
        }

        public void RollBack(object Transasction)
        {
            ((SqlTransaction)Transasction).Rollback();
        }
        public void Commit(object Transasction)
        {
            ((SqlTransaction)Transasction).Commit();
        }


        public string GetValueAsString(DataRow dr, string FieldName)
        {
            if (dr[FieldName] == DBNull.Value)
            {
                return "";
            }
            else
            {
                return (string)dr[FieldName];
            }
        }

        public int GetValueAsInt(DataRow dr, string FieldName)
        {
            if (dr[FieldName] == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return (int)dr[FieldName];
            }
        }


        public decimal GetValueAsDecimal(DataRow dr, string FieldName)
        {
            if (dr[FieldName] == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return (decimal)dr[FieldName];
            }
        }

        public object GetValueAsObject(DataRow dr, string FieldName)
        {
            if (dr[FieldName] == DBNull.Value)
            {
                return null;
            }
            else
            {
                return dr[FieldName];
            }
        }


        public void ExecuteUpdateQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams)
        {
            try
            {
                InternalExecuteUpdateQuery(oTransaction, ConnectionString, TableName, KeyField, KeyId, DatabaseParams, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void ExecuteUpdateQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            try
            {
                InternalExecuteUpdateQuery(oTransaction, ConnectionString, TableName, KeyField, KeyId, DatabaseParams, cmdType);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        void InternalExecuteUpdateQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            string Query = " Update [" + TableName + "] set ";

            if (DatabaseParams == null) DatabaseParams = new DatabaseParamCls[0];
            for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
            {
                Query += "[" + DatabaseParams[iIndex].FieldName + "]=@" + DatabaseParams[iIndex].FieldName;
                if (iIndex < DatabaseParams.Length - 1)
                {
                    Query += ",";
                }
            }
            Query += " where [" + KeyField + "]=@KeyField";

            if (ConnectionString != null)
            {
                _ConnectionString = ConnectionString;
            }
            bool HasTrans = false;
            SqlConnection sqlConnection = null;
            if (oTransaction == null)
            {
                HasTrans = false;
                sqlConnection = new SqlConnection(_ConnectionString);
                sqlConnection.Open();
                oTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = ((SqlTransaction)oTransaction).Connection;
                HasTrans = true;
            }

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = (SqlTransaction)oTransaction;
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = cmdType;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            try
            {
                sqlCommand.Parameters.Add("@KeyField", SqlDbType.VarChar).Value = KeyId;
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false) ((SqlTransaction)oTransaction).Commit();
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    ((SqlTransaction)oTransaction).Rollback();
                }
                string Msg = ex.Message.ToString();
                Msg = CorrectMsg(Msg);
                throw new Exception(Msg);
            }
        }

        public void ExecuteInsertQuery(object oTransaction, string ConnectionString, string TableName, DatabaseParamCls[] DatabaseParams)
        {
            try
            {
                InternalExecuteInsertQuery(oTransaction, ConnectionString, TableName, DatabaseParams, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void ExecuteInsertQuery(object oTransaction, string ConnectionString, string TableName, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            try
            {
                InternalExecuteInsertQuery(oTransaction, ConnectionString, TableName, DatabaseParams, cmdType);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        void InternalExecuteInsertQuery(object oTransaction, string ConnectionString, string TableName, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            string Query = " Insert into [" + TableName + "](";
            string LastQuery = " values (";
            if (DatabaseParams == null) DatabaseParams = new DatabaseParamCls[0];
            for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
            {
                Query += "[" + DatabaseParams[iIndex].FieldName + "]";
                LastQuery += "@" + DatabaseParams[iIndex].FieldName;
                if (iIndex < DatabaseParams.Length - 1)
                {
                    Query += ",";
                    LastQuery += ",";
                }
            }
            Query += ")";
            LastQuery += ")";


            if (ConnectionString != null)
            {
                _ConnectionString = ConnectionString;
            }
            bool HasTrans = false;
            SqlConnection sqlConnection = null;
            if (oTransaction == null)
            {
                HasTrans = false;
                sqlConnection = new SqlConnection(_ConnectionString);
                sqlConnection.Open();
                oTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = ((SqlTransaction)oTransaction).Connection;
                HasTrans = true;
            }

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = (SqlTransaction)oTransaction;
            sqlCommand.CommandText = Query + LastQuery;
            sqlCommand.CommandType = cmdType;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false) ((SqlTransaction)oTransaction).Commit();
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    ((SqlTransaction)oTransaction).Rollback();
                }
                string Msg = ex.Message.ToString();
                Msg = CorrectMsg(Msg);
                throw new Exception(Msg);
            }
        }

        public void ExecuteDeleteQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams)
        {
            try
            {
                InternalExecuteDeleteQuery(oTransaction, ConnectionString, TableName, KeyField, KeyId, DatabaseParams, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void ExecuteDeleteQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            try
            {
                InternalExecuteDeleteQuery(oTransaction, ConnectionString, TableName, KeyField, KeyId, DatabaseParams, cmdType);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        void InternalExecuteDeleteQuery(object oTransaction, string ConnectionString, string TableName, string KeyField, string KeyId, DatabaseParamCls[] DatabaseParams, CommandType cmdType)
        {
            string Query = " Delete from [" + TableName + "] where [" + KeyField + "]=@KeyField";
            if (DatabaseParams == null) DatabaseParams = new DatabaseParamCls[0];
            for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
            {
                Query += " and [" + DatabaseParams[iIndex].FieldName + "]=@" + DatabaseParams[iIndex].FieldName;
            }


            if (ConnectionString != null)
            {
                _ConnectionString = ConnectionString;
            }
            bool HasTrans = false;
            SqlConnection sqlConnection = null;
            if (oTransaction == null)
            {
                HasTrans = false;
                sqlConnection = new SqlConnection(_ConnectionString);
                sqlConnection.Open();
                oTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = ((SqlTransaction)oTransaction).Connection;
                HasTrans = true;
            }

            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = (SqlTransaction)oTransaction;
            sqlCommand.CommandText = Query;
            sqlCommand.CommandType = cmdType;
            sqlCommand.Parameters.Add("@KeyField", SqlDbType.VarChar).Value = KeyId;
            if (DatabaseParams != null)
            {
                for (int iIndex = 0; iIndex < DatabaseParams.Length; iIndex++)
                {
                    string SqlField = DatabaseParams[iIndex].FieldName;
                    sqlCommand.Parameters.Add("@" + SqlField, DatabaseParams[iIndex].FieldType);
                    if (DatabaseParams[iIndex].Value == null)
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlCommand.Parameters["@" + SqlField].Value = DatabaseParams[iIndex].Value;
                    }
                }
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false) ((SqlTransaction)oTransaction).Commit();
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    ((SqlTransaction)oTransaction).Rollback();
                }
                string Msg = ex.Message.ToString();
                Msg = CorrectMsg(Msg);
                throw new Exception(Msg);
            }
        }

    }
}
